#!/usr/bin/env python

from sqlalchemy import Column, Integer, String, Float, Date, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, mapper, relation, relationship, sessionmaker, joinedload_all
from sqlalchemy.orm.collections import attribute_mapped_collection
from sm2 import SM2

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
 
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)  
    fullname = Column(String)
    password = Column(String, nullable=False)
 
    def __init__(self, email_address, fullname, password):
        self.name = name
        self.email_address = email_address
        self.fullname = fullname
        self.password = password
        
class Node(Base):
    
    __tablename__ = "nodes"
 
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(String)
    parent_id = Column(Integer, ForeignKey('nodes.id'))
    children = relationship('Node',
                            cascade = "all",
                            backref = backref("parent", remote_side='Node.id')
                            )
    cards = relationship('Card',
                            cascade = "all",
                            backref = backref("node", remote_side='Node.id')
                            )
     
    def __init__(self, name, parent = None, description = None):
        self.name = name
        self.description = description
        self.parent = parent
 

class Card(Base):
    
    __tablename__ = "cards"
    id = Column(Integer, primary_key=True)
    question = Column(String, nullable=False)
    hint = Column(String)
    answer = Column(String)
    node_id = Column(Integer, ForeignKey('nodes.id'))
 
    def __init__(self, question, hint = None, answer = None, node = None):
        self.question = question
        self.hint = hint
        self.answer = answer
        self.node = node
 
 
class Statistic(Base, SM2):
    
    __tablename__ = "statistics"
    
    id = Column(Integer, primary_key=True)
    easiness_factor = Column(Float, nullable=False)
    number_repetitions = Column(Integer, nullable=False)
    quality_of_last_recall = Column(Integer)
    repetition_interval = Column(Integer)
    next_repetition = Column(Date)
    last_studied = Column(Date)
    
    node_id = Column(Integer, ForeignKey('cards.id'))
    user_id = Column(Integer, ForeignKey('users.id'))
    
    def __init__(self):
        pass
    
def dump_tree(node, indent=0):
    print "   " * indent + node.name
    for c in node.cards:
        print "   " * (indent + 1) + c.question.encode("utf-8")
        
    for c in node.children:
        dump_tree(c, indent +1)

def init_engine():
    engine = create_engine("sqlite:///../davinci.db", echo=False)
    Base.metadata.create_all(engine)
    
    return engine

if __name__ == '__main__':
    
    session = sessionmaker(engine, expire_on_commit=False)()
    r = Node('root')
    r.children.append(Node('c1'))
    r.children.append(Node('c2'))
    session.add(r)
    session.commit()
    
    r = session.query(Node).\
                    options(joinedload_all("children", "children", 
                                            "children", "children")).\
                    filter(Node.name=="root").\
                    first()
    
    print dump_tree(r)
